#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : house_specify.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-08-29 10:37 
@Desc    : 
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)
import pandas as pd
from report_system.utils import db_util
from report_system.utils import date_util
from report_system.utils.log_util import log


def base():
    sql = """select 
                concat(lh.lodge_id) 房源id,
                concat(lh.id,"") 房屋id,
                city.short_name 城市,
                uosc.sname 服务中心,
                uo.name 门店,
                date(lhot.create_time) 上线时间,
                if(datediff(curdate(),date(lhot.create_time)) <15 ,'是','') 15天内上线,
                l.title 房源标题,
                u1.realname  线上销售,
                u2.realname  线下管家,
                lh.room_num 居室,
                if(lh.join_type = 1,"是","") 是否投资人房子,
                if(lh.join_type = 1,u3.realname,"") 投资人姓名

                from youjia.lod_house lh 
                left join youjia.lodge l on l.id = lh.lodge_id
                left join youjia.city on city.id = lh.city_id 
                left join youjia.user_org uo on uo.id = lh.work_id and uo.is_delete = 0
                left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id and uosc.is_deleted = 0
                left join youjia.lod_house_online_time lhot on lhot.lod_house_id = lh.id 
                left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0
                LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
                left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0
                LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
                left join youjia.users u3 on u3.id = lh.landlord_id
                where lh.city_id <> 376
                and lh.state = 100
                and l.state = 0
                and l.state_super = 10
                group by lh.id"""
    df = pd.read_sql(sql, db_util.con_youjia)
    log.info("获取房源基础信息。。。。。")
    return df


def third_lodge_id():
    sql = """select 
    concat(l.id,"") 房源id,
    ltl3.third_lodge_id 途家id,
    ltl25.third_lodge_id 榛果id,
    concat(aul.listing_id,'') 爱彼迎id
    from youjia.lodge l 
    left join lod_third_lodge ltl3 on ltl3.lodge_id = l.id and ltl3.sales_channel_id = 3
    left join lod_third_lodge ltl25 on ltl25.lodge_id = l.id and ltl25.sales_channel_id = 25
    left join airbnb_upload_lodge aul on aul.lodge_id = l.id
    group by l.id"""
    df = pd.read_sql(sql, db_util.con_youjia)
    log.info("获取房源途家、榛果、Airbnb 第三方房源id信息。。。。。")

    return df


def spider_info(dt=None):
    dt = dt if dt else date_util.curdate(0)
    sql = """SELECT 
            concat(lodge_id,"") 房源id ,
            sc.name 渠道,
            if(s1.online = 0,'是','') 下线,
            if(available = 0,'是','') 不可订,
            city_order 城市排名,
            city_order_app APP城市排名,
            price 价格,
            rating 评分,
            review_count 评论数,
            bad_review_count 非五星好评数（平台定义为差评）
            FROM youjia_tpp.sales_001 s1
            left join youjia_common.sales_channel sc on sc.id = s1.platform_id
            where dt = '{dt}'
            """.format(dt=dt)
    df = pd.read_sql(sql, db_util.con_youjia)
    log.info("获取房源途家、榛果、Airbnb 爬虫信息。。。。。")
    return df
